﻿//Mr Inventory by David Mauler 10.2.2010
//Mr Inventory is release under the 
//GNU General Public License version 2 (GPLv2)

using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;
using System.Collections;

public class ServerSql
{
    private string conStr = WebConfigurationManager.ConnectionStrings["sqlcon"].ConnectionString;

	public ServerSql()
	{

	}

    public string addServr(ServerInv server)
    { 
        using (SqlConnection sqlCon = new SqlConnection(conStr))
        {
            try
            {
                sqlCon.Open();

                SqlCommand cmd = new SqlCommand("spInsertServ", sqlCon);

                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@name", server.Name);
                cmd.Parameters.AddWithValue("@os", server.Os);
                cmd.Parameters.AddWithValue("@software", server.Software);
                cmd.Parameters.AddWithValue("@vm", server.Vm);
                cmd.Parameters.AddWithValue("@ip", server.Ip);
                cmd.Parameters.AddWithValue("@model", server.Model);
                cmd.Parameters.AddWithValue("@serial", server.Serial);
                cmd.Parameters.AddWithValue("@cpu", server.Cpu);
                cmd.Parameters.AddWithValue("@mem", server.Mem);
                cmd.Parameters.AddWithValue("@hd", server.Storage);
                cmd.Parameters.AddWithValue("@location", server.Location);
                cmd.Parameters.AddWithValue("@notes", server.Notes);

                cmd.ExecuteNonQuery();

                return "Success! Server " + server.Name + " added to inventory";
            }
            catch (Exception err)
            {
                //Code that dumps to event viewer goes here
                return err.Message;
            }
            finally
            {
                sqlCon.Close();
            }
        }
    }

    public string updateServr(ServerInv server)
    {
        using (SqlConnection sqlCon = new SqlConnection(conStr))
        {
            try
            {
                sqlCon.Open();

                SqlCommand sqlCmd = new SqlCommand("spUpdateServ", sqlCon);
                sqlCmd.CommandType = CommandType.StoredProcedure;

                sqlCmd.Parameters.AddWithValue("@id", server.Id);
                sqlCmd.Parameters.AddWithValue("@name", server.Name);
                sqlCmd.Parameters.AddWithValue("@os", server.Os);
                sqlCmd.Parameters.AddWithValue("@software", server.Software);
                sqlCmd.Parameters.AddWithValue("@vm", server.Vm);
                sqlCmd.Parameters.AddWithValue("@ip", server.Ip);
                sqlCmd.Parameters.AddWithValue("@model", server.Model);
                sqlCmd.Parameters.AddWithValue("@serial", server.Serial);
                sqlCmd.Parameters.AddWithValue("@cpu", server.Cpu);
                sqlCmd.Parameters.AddWithValue("@mem", server.Mem);
                sqlCmd.Parameters.AddWithValue("@hd", server.Storage);
                sqlCmd.Parameters.AddWithValue("@location", server.Location);
                sqlCmd.Parameters.AddWithValue("@notes", server.Notes);

                sqlCmd.ExecuteNonQuery();

                return "Success! Server " + server.Name + " added to inventory";
            }
            catch (Exception err)
            {
                return err.Message;
            }
            finally
            {
                sqlCon.Close();
            }
        }
    }

    public string deleteServr(string server)
    {
        //spDelServ
        using (SqlConnection sqlCon = new SqlConnection(conStr))
        {
            try
            {
                sqlCon.Open();

                SqlCommand sqlCmd = new SqlCommand("spDelServ", sqlCon);
                sqlCmd.CommandType = CommandType.StoredProcedure;

                sqlCmd.Parameters.Add("@name", server);
                sqlCmd.ExecuteNonQuery();

                return "Success! Server " + server + " deleted!";
            }
            catch (Exception err)
            {
                return err.Message;
            }
            finally
            {
                sqlCon.Close();
            }
        }
    }

    public ArrayList findByName(string server)
    {
        using(SqlConnection sqlCon = new SqlConnection(conStr))
        {
            try
            {
                sqlCon.Open();

                ArrayList servList = new ArrayList();
                SqlCommand sqlCmd = new SqlCommand("spFindServName", sqlCon);

                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.Parameters.AddWithValue("@name", server);

                SqlDataReader sqlRead = sqlCmd.ExecuteReader();
 
                while (sqlRead.Read())
                {
                    servList.Add(sqlRead["name"].ToString());
                }

                if (servList.Count == 0)
                {                                  
                    return null;
                }

                return servList;
            }
            catch (Exception err)
            {                
                return null;
            }
            finally
            {
                sqlCon.Close();
            }
        }
    }

    public ServerInv getByName(string server)
    {
        using (SqlConnection sqlCon = new SqlConnection(conStr))
        {
            try
            {
                sqlCon.Open();

                ServerInv returnServ = null;

                SqlCommand sqlCmd = new SqlCommand("spGetByName", sqlCon);
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.Parameters.AddWithValue("@name", server);

                SqlDataReader sqlRead = sqlCmd.ExecuteReader();

                while (sqlRead.Read())
                {
                    returnServ = new ServerInv(sqlRead["name"].ToString(), sqlRead["id"].ToString(), sqlRead["os"].ToString(),
                                               sqlRead["software"].ToString(), sqlRead["vm"].ToString(),
                                               sqlRead["ip"].ToString(), sqlRead["model"].ToString(),
                                               sqlRead["serial"].ToString(), sqlRead["cpu"].ToString(),
                                               sqlRead["mem"].ToString(), sqlRead["hd"].ToString(),
                                               sqlRead["location"].ToString(), sqlRead["notes"].ToString());
                }

                return returnServ;             
            }
            catch (Exception err)
            {
                return null;
            }
            finally
            {
                sqlCon.Close();
            }
        }
    }

    public int getById(string server)
    {
        //This function currently does nothing
        return 0;
    }

    public ArrayList getAllServ()
    {
        using(SqlConnection sqlCon = new SqlConnection(conStr))
        {
            try
            {
                ArrayList servColl = new ArrayList();
                ServerInv server = null;

                sqlCon.Open();

                SqlCommand sqlCmd = new SqlCommand("spGetAllServ", sqlCon);
                SqlDataReader sqlRead = sqlCmd.ExecuteReader();

                while (sqlRead.Read())
                {
                    server = new ServerInv(sqlRead["name"].ToString(), sqlRead["os"].ToString(),
                                           sqlRead["software"].ToString(), sqlRead["vm"].ToString(),
                                           sqlRead["ip"].ToString(), sqlRead["model"].ToString(),
                                           sqlRead["serial"].ToString(), sqlRead["cpu"].ToString(),
                                           sqlRead["mem"].ToString(), sqlRead["hd"].ToString(),
                                           sqlRead["location"].ToString(), sqlRead["notes"].ToString());

                    servColl.Add(server);
                }
                                 
                return servColl;
            }
            catch (Exception err)
            {
                return null;
            }
            finally
            {
                sqlCon.Close();
            }
        }
    }

    public string genSprdSheet()
    {
        using (SqlConnection sqlCon = new SqlConnection(conStr))
        {
            try
            {
                string colNames = null;

                sqlCon.Open();

                SqlCommand sqlCmd = new SqlCommand("spGetColNames", sqlCon);
                sqlCmd.CommandType = CommandType.StoredProcedure;

                SqlDataReader sqlReadCol = sqlCmd.ExecuteReader();

                while (sqlReadCol.Read())
                {                    
                    colNames += sqlReadCol["column_name"].ToString() + ", ";
                }

                colNames += "\n";               

                sqlCon.Close();
                sqlCon.Open();

                //SqlCommand sqlCmdServ = new SqlCommand("spGetAllServ",sqlCon);
                sqlCmd = new SqlCommand("spGetAllServ", sqlCon);
                SqlDataReader sqlReadServ = sqlCmd.ExecuteReader();

                while (sqlReadServ.Read())
                {
                    colNames += sqlReadServ["id"].ToString().Replace(",", string.Empty) + ", "
                             +  sqlReadServ["name"].ToString().Replace(",", string.Empty) + ", "
                             +  sqlReadServ["os"].ToString().Replace(",", string.Empty) + ", "
                             +  sqlReadServ["software"].ToString().Replace(",", string.Empty) + ", "
                             +  sqlReadServ["vm"].ToString().Replace(",", string.Empty) + ", "
                             +  sqlReadServ["ip"].ToString().Replace(",", string.Empty) + ", "
                             +  sqlReadServ["model"].ToString().Replace(",", string.Empty) + ", "
                             +  sqlReadServ["serial"].ToString().Replace(",", string.Empty) + ", "
                             +  sqlReadServ["cpu"].ToString().Replace(",", string.Empty) + ", "
                             +  sqlReadServ["mem"].ToString().Replace(",", string.Empty) + ", "
                             +  sqlReadServ["hd"].ToString().Replace(",", string.Empty) + ", "
                             +  sqlReadServ["location"].ToString().Replace(",", string.Empty) + ", "
                             +  sqlReadServ["notes"].ToString().Replace(",", string.Empty) + "\n";
                }

                return colNames + " "  ; 
            }
            catch (Exception err)
            {
                return err.Message;
            }
            finally
            {
                sqlCon.Close();
            }
        }
    }

    private string debugDispInput(string name, string os, string softW, string vm,
                                  string ip, string model, string serialN, string cpu, 
                                  string mem, string storage, string locat, string notes)
    {
        string testReturn = "<p>" + name + "<br />" +
                                    os + "<br />" +
                                    softW + "<br />" +
                                    vm + "<br />" +
                                    ip + "<br />" +
                                    model + "<br />" +
                                    serialN + "<br />" +
                                    cpu + "<br />" +
                                    mem + "<br />" +
                                    storage + "<br />" +
                                    locat + "<br />" +
                                    notes + "</p> ";
        return "";
    }

}
